package com.mf.framework.component.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 * User: zhaoming
 * DateTime: 2014-12-15
 **/
public class ExcelReaderUtils {

    private final static String FILE_EXT_XSL = "xls";
    private final static String FILE_EXT_XSLX = "xlsx";

    /**
     * 读取文件
     */
    public static <T> List<T> read(String filePath, Map<Integer, String> attrMappingMap, Class<T> obj) throws Exception {
        List<T> ts = new ArrayList<T>();
        InputStream inputStream = new FileInputStream(filePath);
        //扩展名
        String ext = filePath.substring(filePath.lastIndexOf(".") + 1);
        //选择解析方式
        if(ext.toLowerCase().equals(FILE_EXT_XSL)){
            ts = readXls(inputStream, attrMappingMap, obj);
        }else if(ext.toLowerCase().equals(FILE_EXT_XSLX)){
            ts = readXlsx(inputStream, attrMappingMap, obj);
        }
        return ts;
    }

    public static <T> List<T> readXls(InputStream inputStream, Map<Integer, String> attrMappingMap, Class<T> obj) throws Exception {
        List<T> ts = new ArrayList<T>();

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                T t = obj.newInstance();
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 循环列Cell
                for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
                    HSSFCell hssfCell = hssfRow.getCell(cellNum);
                    if (hssfCell == null) {
                        continue;
                    }
                    if(attrMappingMap.containsKey(cellNum)){
                        Field field = obj.getDeclaredField(attrMappingMap.get(cellNum));
                        field.setAccessible(true);
                        if(field.getType().equals(String.class)){
                            field.set(t, hssfCell.getStringCellValue());
                        }else if(field.getType().equals(Integer.class)){
                            field.set(t, (int)hssfCell.getNumericCellValue());
                        }else if(field.getType().equals(Long.class)){
                            field.set(t, (long)hssfCell.getNumericCellValue());
                        }else if(field.getType().equals(Float.class)){
                            field.set(t, (float)hssfCell.getNumericCellValue());
                        }else if(field.getType().equals(Double.class)){
                            field.set(t, hssfCell.getNumericCellValue());
                        }else if(field.getType().equals(BigDecimal.class)){
                            field.set(t, new BigDecimal(hssfCell.getNumericCellValue()));
                        }else if(field.getType().equals(Date.class)){
                            field.set(t, hssfCell.getDateCellValue());
                        }
                    }
                }
                ts.add(t);
            }
        }
        return ts;
    }

    public static  <T> List<T> readXlsx(InputStream inputStream, Map<Integer, String> attrMappingMap, Class<T> obj) throws Exception {
        List<T> ts = new ArrayList<T>();

        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        // 循环工作表Sheet
        for(int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++){
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if(xssfSheet == null){
                continue;
            }
            // 循环行Row
            for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++ ){
                T t = obj.newInstance();
                XSSFRow xssfRow = xssfSheet.getRow( rowNum);
                if(xssfRow == null){
                    continue;
                }
                // 循环列Cell
                for(int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++){
                    XSSFCell xssfCell = xssfRow.getCell( cellNum);
                    if(xssfCell == null){
                        continue;
                    }
                    if(attrMappingMap.containsKey(cellNum)){
                        Field field = obj.getClass().getDeclaredField(attrMappingMap.get(cellNum));
                        field.setAccessible(true);
                        if(field.getType().equals(String.class)){
                            field.set(t, xssfCell.getStringCellValue());
                        }else if(field.getType().equals(Integer.class)){
                            field.set(t, (int)xssfCell.getNumericCellValue());
                        }else if(field.getType().equals(Long.class)){
                            field.set(t, (long)xssfCell.getNumericCellValue());
                        }else if(field.getType().equals(Float.class)){
                            field.set(t, (float)xssfCell.getNumericCellValue());
                        }else if(field.getType().equals(Double.class)){
                            field.set(t, xssfCell.getNumericCellValue());
                        }else if(field.getType().equals(BigDecimal.class)){
                            field.set(t, new BigDecimal(xssfCell.getNumericCellValue()));
                        }else if(field.getType().equals(Date.class)){
                            field.set(t, xssfCell.getDateCellValue());
                        }
                    }
                }
                ts.add(t);
            }
        }
        return ts;
    }

    public static void main(String[] args) throws Exception {
//        Map<Integer, String> attrMappingMap = new HashMap<Integer, String>();
//        attrMappingMap.put(0, "id");
//        attrMappingMap.put(1, "userName");
//        attrMappingMap.put(2, "age");
//        attrMappingMap.put(3, "money");
//        attrMappingMap.put(4, "createTime");
//        List<Aa> aas = ExcelReaderUtils.read("c:\\bb.xls", attrMappingMap, Aa.class);
//        for(Aa aa : aas){
//            System.out.println(JSONObject.toJSON(aa));
//        }
    }

}